﻿Option Explicit On

Imports MySql.Data.MySqlClient
Imports Microsoft.Office.Interop
Imports System.Threading
Public Class frmview_depotclean
    Dim mySqlCommand As New MySqlCommand
    Dim mySqlAdaptor As New MySqlDataAdapter
    Dim mySqlReader As MySqlDataReader
    Dim respone As Object
    Dim mysql As MySqlConnection = main_form.mysqlconection
    Dim iddepot As String
    Dim idctnnew As String
    Dim iddepotdetail As String = 0
    Dim containerString As String
    Dim iddepotunit As String
    Dim ctnstring As String
    Public Delegate Sub DelegateSub(ByVal x As Integer)
    Dim hrtotal As Integer = 0
    Dim departotal As Integer = 0
    Dim sumtotal As Integer = 0
    Dim inbtIndex As Integer

    Private Sub frmview_depotclean_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US")
        System.Threading.Thread.CurrentThread.CurrentUICulture = System.Threading.Thread.CurrentThread.CurrentCulture
        mysql.Close()
        Dim statstrdepot As String
        Dim strdatee As String
        Dim strdates As String
        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select * from voyage join ctnmain on voyage.VOYAGEID = ctnmain.CTNVOYN join ctndepot on ctnmain.CTNMAINID = ctndepot.CTNID where  CTNSTATDEPOT > '1' and CTNSTAT = '0' and CTNSTATCHECK ='0';"        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())

                If mySqlReader("CTNSTATDEPOT") = 1 Then

                    statstrdepot = "รอตรวจสอบ"
                ElseIf mySqlReader("CTNSTATDEPOT") = 2 Then

                    statstrdepot = "ทำความสะอาด"
                ElseIf mySqlReader("CTNSTATDEPOT") = 3 Then

                    statstrdepot = "รอซ่อม"
                Else
                    statstrdepot = mySqlReader("CTNAGENT")
                End If


                If mySqlReader("CTNDEDATES") Is DBNull.Value Then
                    strdates = "-"
                Else
                    strdates = mySqlReader("CTNDEDATES")

                End If
                If mySqlReader("CTNDEDATEE") Is DBNull.Value Then
                    strdatee = "-"
                Else
                    strdatee = mySqlReader("CTNDEDATEE")

                End If

                DataGridViewX2.Rows.Add({Format(mySqlReader("VOYAGEID"), "000"), mySqlReader("CTNSTRING"), mySqlReader("CTNSIZE"), mySqlReader("CTNAGENT"), statstrdepot, strdates, strdatee, mySqlReader("CTNDAYRE"), mySqlReader("CTNDEPOTID"), mySqlReader("CTNMAINID")})




            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()


      





    End Sub

    Private Sub excelreport()
        Dim pathExcel As String
        Dim count As Integer = 7


        pathExcel = FolderBrowserDialog1.SelectedPath
        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add

        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)

        With excelsheets
            Try
                .Shapes.AddPicture(AppDomain.CurrentDomain.BaseDirectory.ToString + "ykp_logo.jpg", False, True, 1, 1, 70, 70)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
            .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
            .PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            .DisplayGridLines = False
            .Range("A1").ColumnWidth = 6.38
            .Range("B1").ColumnWidth = 12.0
            .Range("C1").ColumnWidth = 6.13
            .Range("D1").ColumnWidth = 13.5
            .Range("E1").ColumnWidth = 47.88
            .Range("F1").ColumnWidth = 14.5
            .Range("G1").ColumnWidth = 5.25
            .Range("H1").ColumnWidth = 10.38

            .Range("A1:L200").Font.Name = "Angsana New"
            .Range("A1:L200").Font.Size = 16

            .Rows("1:1").rowheight = 32.25
            .Rows("2:2").rowheight = 28.5
            .Rows("3:4").rowheight = 20.25
            .Rows("5:5").rowheight = 32.25
            .Rows("6:6").rowheight = 21.75
            .Rows("7:8").rowheight = 18
            .Rows("9:9").rowheight = 20.25
            .Rows("10:10").rowheight = 28.5
            .Rows("11:11").rowheight = 18.75
            .Rows("12:16").rowheight = 27
            .Rows("17:41").rowheight = 23.25

            For J = 7 To 10
                .Range("A6:B7").Borders(J).Weight = 3 ' xlThin
                .Range("C6:C7").Borders(J).Weight = 3 ' xlThin
                .Range("D6:D7").Borders(J).Weight = 3 ' xlThin
                .Range("E6:E7").Borders(J).Weight = 3 ' xlThin
                .Range("F6:F7").Borders(J).Weight = 3 ' xlThin
                .Range("G6:H7").Borders(J).Weight = 3 ' xlThin

                .Range("A8:B8").Borders(J).Weight = 3 ' xlThin
                .Range("C8:C8").Borders(J).Weight = 3 ' xlThin
                .Range("D8:D8").Borders(J).Weight = 3 ' xlThin
                .Range("E8:E8").Borders(J).Weight = 3 ' xlThin
                .Range("F8:F8").Borders(J).Weight = 3 ' xlThin
                .Range("G8:H8").Borders(J).Weight = 3 ' xlThin

                '-----
                .Range("A10:A10").Borders(J).Weight = 3 ' xlThin
                .Range("B10:E10").Borders(J).Weight = 3 ' xlThin
                .Range("F10:F10").Borders(J).Weight = 3 ' xlThin
                .Range("G10:H10").Borders(J).Weight = 3 ' xlThin

                .Range("A11:A11").Borders(J).Weight = 3 ' xlThin
                .Range("B11:E11").Borders(J).Weight = 3 ' xlThin
                .Range("F11:F11").Borders(J).Weight = 3 ' xlThin
                .Range("G11:H11").Borders(J).Weight = 3 ' xlThin

                '----
                .Range("A31:G31").Borders(J).Weight = 3 ' xlThin
                .Range("H31:H31").Borders(J).Weight = 3 ' xlThin
                .Range("I31:I31").Borders(J).Weight = 3 ' xlThin
                .Range("J31:J31").Borders(J).Weight = 3 ' xlThin
            Next


            .Range("A11:A27").Borders(7).Weight = 3 ' xlThin
            .Range("B11:B27").Borders(7).Weight = 3 ' xlThin
            .Range("E11:E27").Borders(7).Weight = 3 ' xlThin
            .Range("F11:F27").Borders(7).Weight = 3 ' xlThin

            .Range("H11:H27").Borders(7).Weight = 3 ' xlThin
            .Range("I11:I27").Borders(7).Weight = 3 ' xlThin

            .Range("A32:H32").Borders(9).Weight = 2 ' xlThin
            .Range("A38:H38").Borders(9).Weight = 2 ' xlThin
            .Range("A41:H41").Borders(9).Weight = 2 ' xlThin


            With .Range("A1:H1")
                .Merge()
                .Font.Size = 36
                .Value = "บริษัท วายเคพี ดีโป จำกัด"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A2:H2")
                .Merge()
                .Font.Size = 22
                .Value = "YKP DEPOT CO.,LTD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A3:H3")
                .Merge()
                .Font.Size = 22
                .Value = "88 หมู่ 5 ตำบลบ่อน้ำร้อน อำเภอกันตัง จังหวัดตรัง 92110"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
            End With
            With .Range("A4:H4")
                .Merge()
                .Font.Size = 22
                .Value = "88 MOO 5 BONAMRON, KANTANG, TRANG 92110"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
            End With


            With .Range("A6:B6")
                .Merge()
                .Value = "หมายเลขตู้"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A7:B7")
                .Merge()
                .Value = "Container No."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("C6:C6")
                .Merge()
                .Value = "ขนาดตู้"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("C7:C7")
                .Merge()
                .Value = "Size"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("D6:D6")
                .Merge()
                .Value = "วันที่ตรวจเช็ค"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D7:D7")
                .Merge()
                .Value = "Date-check"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("E6:E6")
                .Merge()
                .Value = "ชื่อท่าเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("E7:E7")
                .Merge()
                .Value = "Port Name"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("F6:F6")
                .Merge()
                .Value = "วันที่ถึงท่าเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("F7:F7")
                .Merge()
                .Value = "Date In"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("G6:H6")
                .Merge()
                .Value = "ชื่อเรือ & เที่ยวเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("F7:F7")
                .Merge()
                .Value = "Vessel & Voyage"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With




            With .Range("A10:A10")
                .Merge()
                .Value = "ลำดับ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A11:A11")
                .Merge()
                .Value = "Item"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With



            With .Range("B10:E10")
                .Merge()
                .Value = "ลักษณะการซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("B11:E11")
                .Merge()
                .Value = "Manner Repairing"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With



            With .Range("F10:F10")
                .Merge()
                .Value = "โค๊ซซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("F11:F11")
                .Merge()
                .Value = "Code  Repair"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With




            With .Range("G10:H10")
                .Merge()
                .Value = "โค๊ซ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("G11:H11")
                .Merge()
                .Value = "Code"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With










            With .Range("A28:C28")
                .Merge()
                .Font.Size = 10
                .Value = "LOCAL CURRENCY : MYR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
                .Font.Italic = True
            End With

            With .Range("A29:D29")
                .Merge()
                .Font.Size = 10
                .Value = "DAMAGED COST เสียหาย :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("A30:D30")
                .Merge()
                .Font.Size = 10
                .Value = "WEAR & TEAR COST เสื่อมสภาพ :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("A31:D31")
                .Merge()
                .Font.Size = 10
                .Value = "CLEANING COST ทำความสะอาด :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("F31:H31")
                .Merge()
                .Font.Size = 10
                .Value = "Signature :.................."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With

            With .Range("A32:C32")
                .Merge()
                .Font.Size = 10
                .Value = "REPAIR METHOD โค๊ซที่ใช้ในการเขียนเพื่อซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A33:D33")
                .Merge()
                .Font.Size = 10
                .Value = "WELD ------- เชื่อม-------- WLD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A34:D34")
                .Merge()
                .Font.Size = 10
                .Value = "STRAIGHTEN-----ดัด----- STR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A35:D35")
                .Merge()
                .Font.Size = 10
                .Value = "JACK UP --ตั้งศูนย์ประตูใหม่-- JAK"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A36:D36")
                .Merge()
                .Font.Size = 10
                .Value = "STR & WELD ---ดัดและเชื่อม--- S&W"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A37:D37")
                .Merge()
                .Font.Size = 10
                .Value = "PATCH -------ตัดปะ-------- PTH"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With





            With .Range("E33:E33")
                .Merge()
                .Font.Size = 10
                .Value = "SECTION -------ตัดต่อ--------- SEC"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E34:E34")
                .Merge()
                .Font.Size = 10
                .Value = "REPLACE ------เปลี่ยน----------- RPL"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E35:E35")
                .Merge()
                .Font.Size = 10
                .Value = "INSERT -------ตัดและสอด--------- INS"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E36:E36")
                .Merge()
                .Font.Size = 10
                .Value = "REFIT OR RESECURE --ถอดประกอบ-- RFT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E37:E37")
                .Merge()
                .Font.Size = 10
                .Value = "REMOVE ---------เอาออก-------- RMV"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With

            With .Range("F33:H33")
                .Merge()
                .Font.Size = 10
                .Value = "FREE -----ทำให้คล่อง------ FRE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A38:B38")
                .Merge()
                .Font.Size = 10
                .Value = "CODE โค๊ซบอกอาการ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A39:E39")
                .Merge()
                .Font.Size = 10
                .Value = "DM --- DAMAGE COST = IMPACTED DAMAGE   เสียหาย"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A40:E40")
                .Merge()
                .Font.Size = 10
                .Value = "WT --- DAMAGE COST = ANY KIND OF WEAR & TEAR   เสื่อมสภาพ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A41:J41")
                .Merge()
                .Font.Size = 10
                .Value = "CN --- CLEANING COST = ANY KIND OF CLEANING   ทำความสะอาด"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With



        End With

        Try
            excelbooks.SaveAs(pathExcel.ToString + "\" + "ใบตรวจงาน YKPDEPOT1  " + Date.Now.Day.ToString + "-" + Date.Now.Month.ToString + "-" + Date.Now.Year.ToString + ".xlsx")


            MsgBox("Report Complete", MsgBoxStyle.Information, "Complete Report")
            excelsheets = Nothing
            excelbooks.Close()


            excelapp.Quit()

            excelbooks = Nothing


            excelapp = Nothing
        Catch ex As Exception
            MsgBox(ex.ToString)
            MsgBox("NOT SAVED")
        End Try



    End Sub
    Private Sub ButtonX1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX1.Click
        searchCTN()
    End Sub
    Public Sub searchCTN()

        Dim statstrdepot As String
        Dim strdatee As String
        Dim strdates As String
        Dim countrepair As Integer
        Dim countwm As Integer
        mysql.Close()
        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If
        mySqlCommand.CommandText = "Select * from voyage left join ctnmain on voyage.VOYAGEID = ctnmain.CTNVOYN left join ctndepot on ctnmain.CTNMAINID = ctndepot.CTNID where  CTNSTATDEPOT > '1' and CTNSTAT = '0' and CTNSTATCHECK ='0' and ( CTNSTRING like '%" & TextBoxX1.Text & "%'  or CTNAGENT like '%" & TextBoxX1.Text & "%') and CTNVOYN ='" & TextBoxX2.Text & "' ;"        ' mySqlCommand.CommandText = 
        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand

        Try
            mySqlReader = mySqlCommand.ExecuteReader


            DataGridViewX2.Rows.Clear()
            While (mySqlReader.Read())





                If mySqlReader("CTNSTATDEPOT") = 1 Then

                    statstrdepot = "รอตรวจสอบ"
                ElseIf mySqlReader("CTNSTATDEPOT") = 2 Then
                    countwm += 1
                    statstrdepot = "ทำความสะอาด"
                ElseIf mySqlReader("CTNSTATDEPOT") = 3 Then
                    countrepair += 1
                    statstrdepot = "รอซ่อม"
                Else
                    statstrdepot = mySqlReader("CTNAGENT")
                End If


                If mySqlReader("CTNDEDATES") Is DBNull.Value Then
                    strdates = "-"
                Else
                    strdates = mySqlReader("CTNDEDATES")

                End If
                If mySqlReader("CTNDEDATEE") Is DBNull.Value Then
                    strdatee = "-"
                Else
                    strdatee = mySqlReader("CTNDEDATEE")

                End If

                DataGridViewX2.Rows.Add({Format(mySqlReader("VOYAGEID"), "000"), mySqlReader("CTNSTRING"), mySqlReader("CTNSIZE"), mySqlReader("CTNAGENT"), statstrdepot, strdates, strdatee, mySqlReader("CTNDAYRE"), mySqlReader("CTNDEPOTID"), mySqlReader("CTNMAINID")})





            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

        Label2.Text = "ตู้ซ่อม  " + countrepair.ToString
        Label4.Text = "ตู้ล้าง  " + countwm.ToString
    End Sub
  
   
    Private Sub TextBoxX1_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBoxX1.KeyDown


        If e.KeyCode = Keys.Enter Then
            searchCTN()
        End If

    End Sub


    Public Sub showlistview3()
     
        hrtotal = 0
        departotal = 0
        sumtotal = 0
        Try
            iddepot = DataGridViewX2.Rows(inbtIndex).Cells(8).Value.ToString
            idctnnew = DataGridViewX2.Rows(inbtIndex).Cells(9).Value.ToString
            containerString = DataGridViewX2.Rows(inbtIndex).Cells(1).Value.ToString
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        DataGridViewX1.Rows.Clear()
        iddepotdetail = "0"
        mysql.Close()
        If mysql.State = ConnectionState.Closed Then
            mysql.Open()
        End If

        mySqlCommand.CommandText = "Select * from ctndepot join ctndepotdet on ctndepot.CTNDEPOTID = ctndepotdet.CTNDEPOTID  where ctndepot.CTNDEPOTID = '" & iddepot & "';"
        ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

        mySqlCommand.Connection = mysql
        mySqlAdaptor.SelectCommand = mySqlCommand
        Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())

                'With ListView3.Items.Add(mySqlReader("CTNDEPOTDETID"))

                '    .SubItems.Add(mySqlReader("depotcode"))
                '    .SubItems.Add(mySqlReader("depotmethod"))
                '    .SubItems.Add(mySqlReader("depotname"))
                '    .SubItems.Add(mySqlReader("CTNCOMMENT"))
                '    .SubItems.Add(mySqlReader("CTNDEPOTSIZE"))
                '    .SubItems.Add(mySqlReader("depotunit"))
                '    .SubItems.Add(mySqlReader("CTNDEPOTHR"))
                '    .SubItems.Add(mySqlReader("CTNPRICE"))
                '    .SubItems.Add(mySqlReader("CTNPRICEDEPART"))

                DataGridViewX1.Rows.Add({mySqlReader("CTNCOMMENT"), mySqlReader("CTNCOMMENTENG"), mySqlReader("CTNCODEREPAIR"), mySqlReader("CTNCODE"), mySqlReader("CTNCODEETC"), mySqlReader("CTNDEPOTHR"), mySqlReader("CTNDEPOTSIZE"), mySqlReader("CTNPRICE"), mySqlReader("CTNPRICEDEPART"), "", "", mySqlReader("CTNDEPOTDETID")})


                '    hrtotal += CInt(mySqlReader("CTNPRICE"))

                '    departotal += CInt(mySqlReader("CTNPRICEDEPART"))
                '    sumtotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))
                '    TextBoxX9.Text = hrtotal
                '    TextBoxX10.Text = departotal
                '    TextBoxX11.Text = sumtotal

                'End With

            End While
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try
        mysql.Close()

        GroupBox1.Text = containerString


    End Sub

    Private Sub ListView1_Click(ByVal sender As Object, ByVal e As System.EventArgs)
       
        iddepotdetail = 0
    End Sub
    Public Sub resetVariable()
    
     
        iddepot = "0"
        idctnnew = "0"
        iddepotdetail = 0
        containerString = "0"
        iddepotunit = "0"

        hrtotal = 0
        departotal = 0
        sumtotal = 0

    End Sub
 
    Private Sub ButtonItem2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
    

    End Sub
    Private Sub excelMalay()
        Dim pathExcel As String
        Dim count As Integer = 7


        pathExcel = FolderBrowserDialog1.SelectedPath
        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add

        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)

        With excelsheets


            Try
                .Shapes.AddPicture(AppDomain.CurrentDomain.BaseDirectory.ToString + "ykp_wanhai.png", False, True, 1, 1, 70, 70)
            Catch ex As Exception

            End Try
            .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
            .PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait
            '.DisplayGridLines = False
            .Range("A1").ColumnWidth = 4.75
            .Range("B1").ColumnWidth = 10.0
            .Range("C1").ColumnWidth = 4.63
            .Range("D1").ColumnWidth = 8.5
            .Range("E1").ColumnWidth = 13.25
            .Range("F1").ColumnWidth = 15.13
            .Range("G1").ColumnWidth = 5.25
            .Range("H1").ColumnWidth = 10.75
            .Range("I1").ColumnWidth = 11.13
            .Range("J1").ColumnWidth = 14.63
            For J = 7 To 10
                .Range("A6:B6").Borders(J).Weight = 3 ' xlThin
                .Range("C6:C6").Borders(J).Weight = 3 ' xlThin
                .Range("D6:D6").Borders(J).Weight = 3 ' xlThin
                .Range("E6:E6").Borders(J).Weight = 3 ' xlThin
                .Range("F6:F6").Borders(J).Weight = 3 ' xlThin
                .Range("G6:H6").Borders(J).Weight = 3 ' xlThin
                .Range("I6:J6").Borders(J).Weight = 3 ' xlThin
                .Range("A7:B7").Borders(J).Weight = 3 ' xlThin
                .Range("C7:C7").Borders(J).Weight = 3 ' xlThin
                .Range("D7:D7").Borders(J).Weight = 3 ' xlThin
                .Range("E7:E7").Borders(J).Weight = 3 ' xlThin
                .Range("F7:F7").Borders(J).Weight = 3 ' xlThin
                .Range("G7:H7").Borders(J).Weight = 3 ' xlThin
                .Range("I7:J7").Borders(J).Weight = 3 ' xlThin

                '-----
                .Range("A10:A10").Borders(J).Weight = 3 ' xlThin
                .Range("B10:E10").Borders(J).Weight = 3 ' xlThin
                .Range("F10:F10").Borders(J).Weight = 3 ' xlThin
                .Range("G10:G10").Borders(J).Weight = 3 ' xlThin
                .Range("H10:H10").Borders(J).Weight = 3 ' xlThin
                .Range("I10:I10").Borders(J).Weight = 3 ' xlThin
                .Range("J10:J10").Borders(J).Weight = 3 ' xlThin
                '----
                .Range("A31:G31").Borders(J).Weight = 3 ' xlThin
                .Range("H31:H31").Borders(J).Weight = 3 ' xlThin
                .Range("I31:I31").Borders(J).Weight = 3 ' xlThin
                .Range("J31:J31").Borders(J).Weight = 3 ' xlThin
            Next
            .Range("A11:A30").Borders(7).Weight = 3 ' xlThin
            .Range("B11:B30").Borders(7).Weight = 3 ' xlThin
            .Range("F11:F30").Borders(7).Weight = 3 ' xlThin
            .Range("G11:G30").Borders(7).Weight = 3 ' xlThin
            .Range("H11:H31").Borders(7).Weight = 3 ' xlThin
            .Range("I11:I31").Borders(7).Weight = 3 ' xlThin
            .Range("J11:J31").Borders(7).Weight = 3 ' xlThin
            .Range("K11:K31").Borders(7).Weight = 3 ' xlThin
            .Range("H39:J39").Borders(9).Weight = 2 ' xlThin
            .Range("A41:J41").Borders(9).Weight = 2 ' xlThin
            .Range("A47:J47").Borders(9).Weight = 2 ' xlThin
            .Range("A48:J48").Borders(9).Weight = 2 ' xlThin
            .Range("A1:L500").Font.Name = "Times New Roman"
            .Range("A1:L500").Font.Size = 12
            .Rows("1:1").rowheight = 30
            .Rows("2:4").rowheight = 20.25
            .Rows("5:9").rowheight = 16.5
            .Rows("10:10").rowheight = 17.25
            .Rows("11:11").rowheight = 15.75

            With .Range("A1:J1")
                .Merge()
                .Font.Size = 24
                .Value = "WAN HAI LINES LTD."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("E7:E7")
                .Merge()
                .Font.Size = 10
                .Value = "YUSOB PORT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A2:J2")
                .Merge()
                .Font.Size = 16
                .Value = "EQUIPMENT REPAIR ESTIMATION REPORT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("I4:I4")
                .Value = "EST NO.PEN"
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With

            With .Range("A6:B6")
                .Merge()
                .Font.Size = 10
                .Value = "CONTAINER NO"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A7:B7")
                .Merge()
                .Font.Size = 10
                .Value = ctnstring
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("C6:C6")
                .Merge()
                .Font.Size = 10
                .Value = "SIZE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D6:D6")
                .Merge()
                .Font.Size = 10
                .Value = "EST.DATE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("E6:E6")
                .Merge()
                .Font.Size = 10
                .Value = "LOADING PORT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("F6:F6")
                .Merge()
                .Font.Size = 10
                .Value = "IMPORT DATE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("G6:H6")
                .Merge()
                .Font.Size = 10
                .Value = "IMP VSL & VOY"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("I6:J6")
                .Merge()
                .Font.Size = 10
                .Value = "TOTAL COST"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A10:A10")
                .Merge()
                .Font.Size = 10
                .Value = "ITEM"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("B10:E10")
                .Merge()
                .Font.Size = 10
                .Value = "DESCRIPTION OF DAMAGE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("F10:F10")
                .Merge()
                .Font.Size = 10
                .Value = "REPAIR METHOD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("G10:G10")
                .Merge()
                .Font.Size = 10
                .Value = "CODE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("H10:H10")
                .Merge()
                .Font.Size = 10
                .Value = "MAN HOUR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("I10:I10")
                .Merge()
                .Font.Size = 10
                .Value = "LABOR COST"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("J10:J10")
                .Merge()
                .Font.Size = 10
                .Value = "MATERIAL COST"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("J31:J31")
                .Merge()
                .Font.Size = 10
                .Value = "TOTAL"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A33:C33")
                .Merge()
                .Font.Size = 10
                .Value = "LOCAL CURRENCY : MYR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
                .Font.Italic = True
            End With

            With .Range("A35:C35")
                .Merge()
                .Font.Size = 10
                .Value = "DAMAGED COST :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("A37:C37")
                .Merge()
                .Font.Size = 10
                .Value = "WEAR & TEAR COST :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("A39:C39")
                .Merge()
                .Font.Size = 10
                .Value = "CLEANING COST :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("H39:I39")
                .Merge()
                .Font.Size = 10
                .Value = "APPROVED BY :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With

            With .Range("A41:C41")
                .Merge()
                .Font.Size = 10
                .Value = "REPAIR METHOD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A42:D42")
                .Merge()
                .Font.Size = 10
                .Value = "WELD ----------------------- WLD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A43:D43")
                .Merge()
                .Font.Size = 10
                .Value = "STRAIGHTEN------------- STR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A44:D44")
                .Merge()
                .Font.Size = 10
                .Value = "JACK UP ------------------- JAK"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A45:D45")
                .Merge()
                .Font.Size = 10
                .Value = "STR & WELD ------------- S&W"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A46:D46")
                .Merge()
                .Font.Size = 10
                .Value = "PATCH --------------------- PTH"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With





            With .Range("E42:F42")
                .Merge()
                .Font.Size = 10
                .Value = "SECTION ---------------------- SEC"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E43:F43")
                .Merge()
                .Font.Size = 10
                .Value = "REPLACE --------------------- RPL"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E44:F44")
                .Merge()
                .Font.Size = 10
                .Value = "INSERT ------------------------ INS"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E45:F45")
                .Merge()
                .Font.Size = 10
                .Value = "REFIT OR RESECURE ----- RFT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E46:F46")
                .Merge()
                .Font.Size = 10
                .Value = "REMOVE ---------------------- RMV"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With

            With .Range("G42:I42")
                .Merge()
                .Font.Size = 10
                .Value = "FREE --------------------------- FRE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A48:B48")
                .Merge()
                .Font.Size = 10
                .Value = "CODE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A49:J49")
                .Merge()
                .Font.Size = 10
                .Value = "DM --- DAMAGE COST = IMPACTED DAMAGE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A50:J50")
                .Merge()
                .Font.Size = 10
                .Value = "WT --- DAMAGE COST = ANY KIND OF WEAR & TEAR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A51:J51")
                .Merge()
                .Font.Size = 10
                .Value = "CN --- CLEANING COST = ANY KIND OF CLEANING"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            Dim countline As Integer = 12
            Dim countitem As Integer = 1
            Dim man_hour As Double = 0
            Dim dmtotal As Integer = 0
            Dim wttotal As Integer = 0
            Dim cntotal As Integer = 0

            hrtotal = 0

            departotal = 0
            sumtotal = 0
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctndepot join ctndepotdet on ctndepot.CTNDEPOTID = ctndepotdet.CTNDEPOTID where ctndepot.CTNDEPOTID = '" & iddepot & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())


                    With .Range("A" + countline.ToString + ":A" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = countitem.ToString
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    countitem += 1

                    With .Range("B" + countline.ToString + ":E" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNCOMMENTENG")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                        .Font.Bold = True

                    End With
                    With .Range("F" + countline.ToString + ":F" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNCODEREPAIR")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With
                    With .Range("G" + countline.ToString + ":G" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNCODE")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    With .Range("H" + countline.ToString + ":H" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNDEPOTHR")
                        .NumberFormat = "#,##0.00"
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    With .Range("I" + countline.ToString + ":I" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNPRICE")
                        .NumberFormat = "#,##0.00"
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    With .Range("J" + countline.ToString + ":J" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNPRICEDEPART")
                        .NumberFormat = "#,##0.00"
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    countline += 1

                    man_hour += CDbl(mySqlReader("CTNDEPOTHR"))

                    hrtotal += CInt(mySqlReader("CTNPRICE"))
                    departotal += CInt(mySqlReader("CTNPRICEDEPART"))
                    sumtotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))

                    If mySqlReader("CTNCODE") = "DM" Then
                        dmtotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))
                    ElseIf mySqlReader("CTNCODE") = "WT" Then
                        wttotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))
                    ElseIf mySqlReader("CTNCODE") = "CN" Then
                        cntotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))
                    End If

                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()




            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctnmain join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID where CTNMAINID = '" & idctnnew & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())


                    With .Range("C7:C7")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNSIZE")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("F7:F7")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("VOYDATEEN")

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("G7:H7")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("VOYVESNAMEN") + "   N" + Format(mySqlReader("VOYAGEID"), "000")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With



                End While

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try



            With .Range("H31:H31")
                .Merge()
                .Font.Size = 10
                .Value = man_hour
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("I31:I31")
                .Merge()
                .Font.Size = 10
                .Value = hrtotal
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("J31:J31")
                .Merge()
                .Font.Size = 10
                .Value = departotal
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D35:D35")
                .Merge()
                .Font.Size = 10
                If dmtotal > 0 Then
                    .Value = dmtotal
                Else

                End If
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D37:D37")
                .Merge()
                .Font.Size = 10
                If wttotal > 0 Then
                    .Value = wttotal
                End If
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D39:D39")
                .Merge()
                .Font.Size = 10
                If cntotal > 0 Then
                    .Value = cntotal
                End If
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("I7:J7")
                .Merge()
                .Font.Size = 10
                .Value = sumtotal
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

        End With
        excelapp.Windows.Application.ActiveWindow.DisplayGridlines = False
        Try
            excelbooks.SaveAs(pathExcel.ToString + "\" + "ReportDepot" + Date.Now.Day.ToString + "-" + Date.Now.Month.ToString + "-" + Date.Now.Year.ToString + ".xlsx")


            MsgBox("Report Complete", MsgBoxStyle.Information, "Complete Report")
            excelsheets = Nothing
            excelbooks.Close()


            excelapp.Quit()

            excelbooks = Nothing


            excelapp = Nothing

        Catch ex As Exception
            MsgBox(ex.ToString)
            MsgBox("NOT SAVED")

        End Try

    End Sub
    Private Sub excelOther()
        Dim pathExcel As String
        Dim count As Integer = 7


        pathExcel = FolderBrowserDialog1.SelectedPath
        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add

        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)

        With excelsheets
            Try
                .Shapes.AddPicture(AppDomain.CurrentDomain.BaseDirectory.ToString + "ykp_logo.jpg", False, True, 1, 1, 70, 70)
            Catch ex As Exception

            End Try
            .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
            .PageSetup.Orientation = Excel.XlPageOrientation.xlLandscape
            .DisplayGridLines = False
            .Range("A1").ColumnWidth = 6.0
            .Range("B1").ColumnWidth = 11.25
            .Range("C1").ColumnWidth = 9.13
            .Range("D1").ColumnWidth = 12.63
            .Range("E1").ColumnWidth = 12.88
            .Range("F1").ColumnWidth = 12.63
            .Range("G1").ColumnWidth = 4.88
            .Range("H1").ColumnWidth = 14.13
            .Range("I1").ColumnWidth = 10.75
            .Range("J1").ColumnWidth = 17.88


            .Range("A1:L200").Font.Name = "Angsana New"
            .Range("A1:L200").Font.Size = 16

            .Rows("1:1").rowheight = 32.25
            .Rows("2:2").rowheight = 28.5
            .Rows("3:4").rowheight = 20.25
            .Rows("5:5").rowheight = 32.25
            .Rows("6:6").rowheight = 21.75
            .Rows("7:8").rowheight = 18
            .Rows("9:9").rowheight = 20.25
            .Rows("10:10").rowheight = 28.5
            .Rows("11:11").rowheight = 18.75
            .Rows("12:41").rowheight = 17


            For J = 7 To 10
                .Range("A6:B7").Borders(J).Weight = 3 ' xlThin
                .Range("C6:C7").Borders(J).Weight = 3 ' xlThin
                .Range("D6:D7").Borders(J).Weight = 3 ' xlThin
                .Range("E6:E7").Borders(J).Weight = 3 ' xlThin
                .Range("F6:F7").Borders(J).Weight = 3 ' xlThin
                .Range("G6:H7").Borders(J).Weight = 3 ' xlThin

                .Range("A8:B8").Borders(J).Weight = 3 ' xlThin
                .Range("C8:C8").Borders(J).Weight = 3 ' xlThin
                .Range("D8:D8").Borders(J).Weight = 3 ' xlThin
                .Range("E8:E8").Borders(J).Weight = 3 ' xlThin
                .Range("F8:F8").Borders(J).Weight = 3 ' xlThin
                .Range("G8:H8").Borders(J).Weight = 3 ' xlThin

                '-----
                .Range("A10:A10").Borders(J).Weight = 3 ' xlThin
                .Range("B10:E10").Borders(J).Weight = 3 ' xlThin
                .Range("F10:F10").Borders(J).Weight = 3 ' xlThin
                .Range("G10:H10").Borders(J).Weight = 3 ' xlThin

                .Range("A11:A11").Borders(J).Weight = 3 ' xlThin
                .Range("B11:E11").Borders(J).Weight = 3 ' xlThin
                .Range("F11:F11").Borders(J).Weight = 3 ' xlThin
                .Range("G11:H11").Borders(J).Weight = 3 ' xlThin

                '----
                .Range("A31:G31").Borders(J).Weight = 3 ' xlThin
                .Range("H31:H31").Borders(J).Weight = 3 ' xlThin
                .Range("I31:I31").Borders(J).Weight = 3 ' xlThin
                .Range("J31:J31").Borders(J).Weight = 3 ' xlThin
            Next


            .Range("A11:A27").Borders(7).Weight = 3 ' xlThin
            .Range("B11:B27").Borders(7).Weight = 3 ' xlThin
            .Range("E11:E27").Borders(7).Weight = 3 ' xlThin
            .Range("F11:F27").Borders(7).Weight = 3 ' xlThin

            .Range("H11:H27").Borders(7).Weight = 3 ' xlThin
            .Range("I11:I27").Borders(7).Weight = 3 ' xlThin

            .Range("A32:H32").Borders(9).Weight = 2 ' xlThin
            .Range("A38:H38").Borders(9).Weight = 2 ' xlThin
            .Range("A41:H41").Borders(9).Weight = 2 ' xlThin


            With .Range("A1:H1")
                .Merge()
                .Font.Size = 36
                .Value = "บริษัท วายเคพี ดีdโป จำกัด"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A2:H2")
                .Merge()
                .Font.Size = 22
                .Value = "YKP DEPOT CO.,LTD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A3:H3")
                .Merge()
                .Font.Size = 22
                .Value = "88 หมู่ 5 ตำบลบ่อน้ำร้อน อำเภอกันตัง จังหวัดตรัง 92110"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
            End With
            With .Range("A4:H4")
                .Merge()
                .Font.Size = 22
                .Value = "88 MOO 5 BONAMRON, KANTANG, TRANG 92110"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
            End With


            With .Range("A6:B6")
                .Merge()
                .Value = "หมายเลขตู้"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A7:B7")
                .Merge()
                .Value = "Container No."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("C6:C6")
                .Merge()
                .Value = "ขนาดตู้"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("C7:C7")
                .Merge()
                .Value = "Size"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("D6:D6")
                .Merge()
                .Value = "วันที่ตรวจเช็ค"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D7:D7")
                .Merge()
                .Value = "Date-check"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("E6:E6")
                .Merge()
                .Value = "ชื่อท่าเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("E7:E7")
                .Merge()
                .Value = "Port Name"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("F6:F6")
                .Merge()
                .Value = "วันที่ถึงท่าเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("F7:F7")
                .Merge()
                .Value = "Date In"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("G6:H6")
                .Merge()
                .Value = "ชื่อเรือ & เที่ยวเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("F7:F7")
                .Merge()
                .Value = "Vessel & Voyage"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With




            With .Range("A10:A10")
                .Merge()
                .Value = "ลำดับ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A11:A11")
                .Merge()
                .Value = "Item"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With



            With .Range("B10:E10")
                .Merge()
                .Value = "ลักษณะการซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("B11:E11")
                .Merge()
                .Value = "Manner Repairing"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With



            With .Range("F10:F10")
                .Merge()
                .Value = "โค๊ซซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("F11:F11")
                .Merge()
                .Value = "Code  Repair"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With




            With .Range("G10:H10")
                .Merge()
                .Value = "โค๊ซ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("G11:H11")
                .Merge()
                .Value = "Code"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With










            With .Range("A28:C28")
                .Merge()
                .Font.Size = 10
                .Value = "LOCAL CURRENCY : MYR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
                .Font.Italic = True
            End With

            With .Range("A29:D29")
                .Merge()
                .Font.Size = 10
                .Value = "DAMAGED COST เสียหาย :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("A30:D30")
                .Merge()
                .Font.Size = 10
                .Value = "WEAR & TEAR COST เสื่อมสภาพ :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("A31:D31")
                .Merge()
                .Font.Size = 10
                .Value = "CLEANING COST ทำความสะอาด :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("F31:H31")
                .Merge()
                .Font.Size = 10
                .Value = "Signature :.................."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With

            With .Range("A32:C32")
                .Merge()
                .Font.Size = 10
                .Value = "REPAIR METHOD โค๊ซที่ใช้ในการเขียนเพื่อซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A33:D33")
                .Merge()
                .Font.Size = 10
                .Value = "WELD ------- เชื่อม-------- WLD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A34:D34")
                .Merge()
                .Font.Size = 10
                .Value = "STRAIGHTEN-----ดัด----- STR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A35:D35")
                .Merge()
                .Font.Size = 10
                .Value = "JACK UP --ตั้งศูนย์ประตูใหม่-- JAK"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A36:D36")
                .Merge()
                .Font.Size = 10
                .Value = "STR & WELD ---ดัดและเชื่อม--- S&W"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A37:D37")
                .Merge()
                .Font.Size = 10
                .Value = "PATCH -------ตัดปะ-------- PTH"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With





            With .Range("E33:E33")
                .Merge()
                .Font.Size = 10
                .Value = "SECTION -------ตัดต่อ--------- SEC"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E34:E34")
                .Merge()
                .Font.Size = 10
                .Value = "REPLACE ------เปลี่ยน----------- RPL"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E35:E35")
                .Merge()
                .Font.Size = 10
                .Value = "INSERT -------ตัดและสอด--------- INS"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E36:E36")
                .Merge()
                .Font.Size = 10
                .Value = "REFIT OR RESECURE --ถอดประกอบ-- RFT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E37:E37")
                .Merge()
                .Font.Size = 10
                .Value = "REMOVE ---------เอาออก-------- RMV"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With

            With .Range("F33:H33")
                .Merge()
                .Font.Size = 10
                .Value = "FREE -----ทำให้คล่อง------ FRE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A38:B38")
                .Merge()
                .Font.Size = 10
                .Value = "CODE โค๊ซบอกอาการ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A39:E39")
                .Merge()
                .Font.Size = 10
                .Value = "DM --- DAMAGE COST = IMPACTED DAMAGE   เสียหาย"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A40:E40")
                .Merge()
                .Font.Size = 10
                .Value = "WT --- DAMAGE COST = ANY KIND OF WEAR & TEAR   เสื่อมสภาพ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A41:J41")
                .Merge()
                .Font.Size = 10
                .Value = "CN --- CLEANING COST = ANY KIND OF CLEANING   ทำความสะอาด"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With



        End With
        excelapp.Windows.Application.ActiveWindow.DisplayGridlines = False
        Try
            excelbooks.SaveAs(pathExcel.ToString + "\" + "ใบตรวจงาน YKPDEPOT1  " + Date.Now.Day.ToString + "-" + Date.Now.Month.ToString + "-" + Date.Now.Year.ToString + ".xlsx")


            MsgBox("Report Complete", MsgBoxStyle.Information, "Complete Report")
            excelsheets = Nothing
            excelbooks.Close()


            excelapp.Quit()

            excelbooks = Nothing


            excelapp = Nothing
        Catch ex As Exception
            MsgBox(ex.ToString)
            MsgBox("NOT SAVED")
        End Try

    End Sub
    Private Sub ButtonItem1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)

        If DataGridViewX2.Rows.Count > 0 Then

            FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
            FolderBrowserDialog1.ShowNewFolderButton = True
            FolderBrowserDialog1.SelectedPath = "C:\"
            If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                Try
                    Dim t = New Thread(New ThreadStart(AddressOf excelreportRepair))
                    t.Start()

                Catch ex As Exception

                End Try
            End If


        Else
            MsgBox("กรุณาเลือกตู้คอนเทนเนอร์")

        End If



    End Sub
    Private Sub excelreportRepair()
        Dim pathExcel As String
        Dim count As Integer = 7


        pathExcel = FolderBrowserDialog1.SelectedPath
        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add

        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)

        With excelsheets
            .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
            .PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait
            Try
                .Shapes.AddPicture(AppDomain.CurrentDomain.BaseDirectory.ToString + "ykp_logo.jpg", False, True, 1, 1, 70, 70)
            Catch ex As Exception

            End Try
            .Range("A1").ColumnWidth = 4.75
            .Range("B1").ColumnWidth = 10.0
            .Range("C1").ColumnWidth = 6.13
            .Range("D1").ColumnWidth = 13.5
            .Range("E1").ColumnWidth = 13.25
            .Range("F1").ColumnWidth = 13.5
            .Range("G1").ColumnWidth = 5.25
            .Range("H1").ColumnWidth = 10.75
            '.DisplayGridLines = False
            .Range("A1:L20").Font.Name = "Angsana New"
            .Range("A1:L20").Font.Size = 14
            .Range("A21:L400").Font.Name = "Times New Roman"
            .Range("A21:L400").Font.Size = 9
            .Rows("4:8").rowheight = 0
            .Rows("9:46").rowheight = 15

            For J = 7 To 10
                .Range("A29:B30").Borders(J).Weight = 3 ' xlThin
                .Range("C29:C30").Borders(J).Weight = 3 ' xlThin
                .Range("D29:D30").Borders(J).Weight = 3 ' xlThin
                .Range("E29:E30").Borders(J).Weight = 3 ' xlThin
                .Range("F29:F30").Borders(J).Weight = 3 ' xlThin
                .Range("G29:H30").Borders(J).Weight = 3 ' xlThin

                .Range("A31:B31").Borders(J).Weight = 3 ' xlThin
                .Range("C31:C31").Borders(J).Weight = 3 ' xlThin
                .Range("D31:D31").Borders(J).Weight = 3 ' xlThin
                .Range("E31:E31").Borders(J).Weight = 3 ' xlThin
                .Range("F31:F31").Borders(J).Weight = 3 ' xlThin
                .Range("G31:H31").Borders(J).Weight = 3 ' xlThin
                '-----
                .Range("A33:A34").Borders(J).Weight = 3 ' xlThin
                .Range("B33:E34").Borders(J).Weight = 3 ' xlThin
                .Range("F33:F34").Borders(J).Weight = 3 ' xlThin
                .Range("G33:H34").Borders(J).Weight = 3 ' xlThin

            Next

            .Range("A31:A48").Borders(7).Weight = 3 ' xlThin
            .Range("B34:B48").Borders(7).Weight = 3 ' xlThin

            .Range("F34:F48").Borders(7).Weight = 3 ' xlThin

            .Range("G34:G48").Borders(7).Weight = 3 ' xlThin
            .Range("I34:I48").Borders(7).Weight = 3 ' xlThin

          
            .Range("A48:H48").Borders(9).Weight = 2 ' xlThin

            With .Range("A1:H1")
                .Merge()
                .Value = "Estimate Of  Repair"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("A29:B29")
                .Merge()
                .Value = "หมายเลขตู้"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A30:B30")
                .Merge()
                .Value = "Container No."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("C29:C29")
                .Merge()
                .Value = "ขนาดตู้"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("C30:C30")
                .Merge()
                .Value = "Size"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("D29:D29")
                .Merge()
                .Value = "วันที่ตรวจเช็ค"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D30:D30")
                .Merge()
                .Value = "Date-check"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("E29:E29")
                .Merge()
                .Value = "ชื่อท่าเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("E30:E30")
                .Merge()
                .Value = "Port Name"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("F29:F29")
                .Merge()
                .Value = "วันที่ถึงท่าเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("F30:F30")
                .Merge()
                .Value = "Date In"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("G29:H29")
                .Merge()
                .Value = "ชื่อเรือ & เที่ยวเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("G30:H30")
                .Merge()
                .Value = "Vessel & Voyage"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With




            With .Range("A33:A33")
                .Merge()
                .Value = "ลำดับ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A34:A34")
                .Merge()
                .Value = "Item"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With



            With .Range("B33:E33")
                .Merge()
                .Value = "ลักษณะการซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("B34:E34")
                .Merge()
                .Value = "Manner Repairing"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With



            With .Range("F33:F33")
                .Merge()
                .Value = "โค๊ซซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("F34:F34")
                .Merge()
                .Value = "Code  Repair"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With




            With .Range("G33:H33")
                .Merge()
                .Value = "โค๊ซ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("G34:H34")
                .Merge()
                .Value = "Code"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With








            With .Range("F50:H50")
                .Merge()
                .Font.Size = 10
                .Value = "Signature :........................."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With


            Dim countline As Integer = 35
            Dim countitem As Integer = 1
            Dim man_hour As Double = 0

            hrtotal = 0

            departotal = 0
            sumtotal = 0
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctndepot join ctndepotdet on ctndepot.CTNDEPOTID = ctndepotdet.CTNDEPOTID  where ctndepot.CTNDEPOTID = '" & iddepot & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())
                With .Range("A" + countline.ToString + ":A" + countline.ToString)
                    .Merge()
                    .Font.Size = 10
                    .Value = countitem.ToString
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True

                End With
                countitem += 1

                With .Range("B" + countline.ToString + ":E" + countline.ToString)
                    .Merge()
                    .Font.Size = 10
                    .Value = mySqlReader("CTNCOMMENT")
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                    .Font.Bold = True

                End With
                With .Range("F" + countline.ToString + ":F" + countline.ToString)
                    .Merge()
                    .Font.Size = 10
                    .Value = mySqlReader("CTNCODEREPAIR")
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                End With
                With .Range("G" + countline.ToString + ":H" + countline.ToString)
                    .Merge()
                    .Font.Size = 10
                    .Value = mySqlReader("CTNCODE")
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True

                End With
                countline += 1
  

            End While

            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctnmain join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID where CTNMAINID = '" & idctnnew & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())


                    With .Range("A31:B31")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNSTRING")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("C31:C31")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNSIZE")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("E31:E31")
                        .Merge()
                        .Font.Size = 10
                        .Value = "YUSOB PORT"

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

                    With .Range("F31:F31")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("VOYDATEEN")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

                    With .Range("G31:H31")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("VOYVESNAMEN") + "   N" + Format(mySqlReader("VOYAGEID"), "000")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("D31:D31")
                        .Merge()
                        .Font.Size = 10
                        .Value = " "
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

                End While

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

        End With

        excelapp.Windows.Application.ActiveWindow.DisplayGridlines = False
        Try
            excelbooks.SaveAs(pathExcel.ToString + "\" + "ใบตรวจงาน YKPDEPOT1" + Date.Now.Day.ToString + "-" + Date.Now.Month.ToString + "-" + Date.Now.Year.ToString + ".xlsx")


            MsgBox("Report Complete", MsgBoxStyle.Information, "Complete Report")
            excelsheets = Nothing
            excelbooks.Close()


            excelapp.Quit()

            excelbooks = Nothing


            excelapp = Nothing

        Catch ex As Exception
            MsgBox(ex.ToString)
            MsgBox("NOT SAVED")

        End Try




    End Sub
    Private Sub ButtonItem3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If DataGridViewX2.Rows.Count > 0 Then

            FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
            FolderBrowserDialog1.ShowNewFolderButton = True
            FolderBrowserDialog1.SelectedPath = "C:\"
            If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then
                Try
                    Dim t = New Thread(New ThreadStart(AddressOf excelOther))
                    t.Start()

                Catch ex As Exception

                End Try
            End If


        Else
            MsgBox("กรุณาเลือกตู้คอนเทนเนอร์")

        End If


    End Sub
    Private Sub ButtonItem5_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If DataGridViewX2.Rows.Count > 0 Then


            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Dim commandText3 As String
            commandText3 = "UPDATE ctnmain SET CTNSTAT = '1' where CTNMAINID = '" & idctnnew & "'; "


            Try
                mySqlCommand.CommandText = commandText3
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = mysql

                mySqlCommand.ExecuteNonQuery()

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()

            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            commandText3 = "UPDATE ctndepot SET CTNSTATCHECK ='1', CTNDEDATEE = '" & Date.Now.ToString("dd-MM-yyyy") & "' where CTNDEPOTID = '" & iddepot & "'; "


            Try
                mySqlCommand.CommandText = commandText3
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = mysql

                mySqlCommand.ExecuteNonQuery()

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()



            MsgBox("ตู้คอนเทนเนอร์พร้อมใช้งานแล้ว")
        Else
            MsgBox("กรุณาเลือกตู้คอนเทนเนอร์ที่ซ่อมเสร็จแล้ว")
        End If
        searchCTN()


    End Sub
    Private Sub ButtonX3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX3.Click
        Dim cf As New frmview_depot
        cf.MdiParent = Me.MdiParent
        Me.Close()
        cf.Dock = DockStyle.Fill
        cf.Show()

    End Sub

    Sub ChangFocus(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyPressEventArgs)
        If e.KeyChar = Microsoft.VisualBasic.ChrW(13) Then
            SendKeys.Send("{TAB}")
            e.Handled = True
        End If
    End Sub

    Private Sub DataGridViewX1_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewX1.CellClick
        If e.RowIndex < 0 Then
            Exit Sub
        End If
        Try
            If DataGridViewX1.Rows(inbtIndex).Cells(11).Value > 0 Then
            Else
                DataGridViewX1.Rows(inbtIndex).Cells(11).Value = "0"
            End If
            inbtIndex = e.RowIndex
            If DataGridViewX1.Rows(inbtIndex).Cells(4).Value <> "" Then
                DataGridViewX1.Rows(inbtIndex).Cells(4).Value = DataGridViewX1.Rows(inbtIndex).Cells(4).Value.ToString.ToUpper
            End If
            DataGridViewX1.Rows(inbtIndex).Selected = True

            DataGridViewX1.Rows(inbtIndex).Cells(7).Value = CDbl(CDbl(DataGridViewX1.Rows(inbtIndex).Cells(6).Value) * CDbl(DataGridViewX1.Rows(inbtIndex).Cells(5).Value)).ToString("#,###.00")

        Catch ex As Exception

        End Try
    End Sub

    Private Sub DataGridViewX1_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewX1.CellEndEdit
        inbtIndex = e.RowIndex
        If e.ColumnIndex = 4 Then

            mysql.Close()

            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            DataGridViewX1.Rows(inbtIndex).Cells(5).Value = "0"
            DataGridViewX1.Rows(inbtIndex).Cells(6).Value = "12"
            If DataGridViewX1.Rows(inbtIndex).Cells(11).Value > 0 Then
            Else
                DataGridViewX1.Rows(inbtIndex).Cells(11).Value = "0"
            End If
            mySqlCommand.CommandText = "Select * from ctndepotunit where depotname ='" & DataGridViewX1.Rows(inbtIndex).Cells(4).Value & "' ;"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())
                    DataGridViewX1.Rows(inbtIndex).Cells(5).Value = mySqlReader("depotmanhr")

                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()

            DataGridViewX1.Rows(inbtIndex).Cells(7).Value = CDbl(CDbl(DataGridViewX1.Rows(inbtIndex).Cells(6).Value) * CDbl(DataGridViewX1.Rows(inbtIndex).Cells(5).Value)).ToString("#,###.00")

        ElseIf e.ColumnIndex = 5 Then

            DataGridViewX1.Rows(inbtIndex).Cells(7).Value = CDbl(CDbl(DataGridViewX1.Rows(inbtIndex).Cells(6).Value) * CDbl(DataGridViewX1.Rows(inbtIndex).Cells(5).Value)).ToString("#,###.00")

        End If
    End Sub


    Private Sub DataGridViewX1_RowLeave(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewX1.RowLeave
        inbtIndex = e.RowIndex
        If e.ColumnIndex = 4 Then
           
            mysql.Close()

            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            DataGridViewX1.Rows(inbtIndex).Cells(5).Value = "0"
            DataGridViewX1.Rows(inbtIndex).Cells(6).Value = "12"
            If DataGridViewX1.Rows(inbtIndex).Cells(11).Value > 0 Then
            Else
                DataGridViewX1.Rows(inbtIndex).Cells(11).Value = "0"
            End If
            mySqlCommand.CommandText = "Select * from ctndepotunit where depotname ='" & DataGridViewX1.Rows(inbtIndex).Cells(4).Value & "' ;"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())
                    DataGridViewX1.Rows(inbtIndex).Cells(5).Value = mySqlReader("depotmanhr")

                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()

            DataGridViewX1.Rows(inbtIndex).Cells(7).Value = CDbl(CDbl(DataGridViewX1.Rows(inbtIndex).Cells(6).Value) * CDbl(DataGridViewX1.Rows(inbtIndex).Cells(5).Value)).ToString("#,###.00")

        ElseIf e.ColumnIndex = 5 Then

            DataGridViewX1.Rows(inbtIndex).Cells(7).Value = CDbl(CDbl(DataGridViewX1.Rows(inbtIndex).Cells(6).Value) * CDbl(DataGridViewX1.Rows(inbtIndex).Cells(5).Value)).ToString("#,###.00")

        End If


    End Sub
    Private Sub Column7_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Column7.Click

        If DataGridViewX1.Rows(inbtIndex).Cells(11).Value <> "0" Then


            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try
                Dim commandText2 As String
                commandText2 = "UPDATE ctndepotdet SET  CTNDEPOTID = '" & iddepot & "' , CTNCOMMENT = '" & DataGridViewX1.Rows(inbtIndex).Cells(0).Value & "' , CTNPRICE = '" & DataGridViewX1.Rows(inbtIndex).Cells(7).Value & "' , CTNPRICEDEPART = '" & DataGridViewX1.Rows(inbtIndex).Cells(8).Value & "' , CTNDEPOTSIZE = '" & DataGridViewX1.Rows(inbtIndex).Cells(6).Value & "' , CTNDEPOTHR = '" & DataGridViewX1.Rows(inbtIndex).Cells(5).Value & "',CTNCOMMENTENG = '" & DataGridViewX1.Rows(inbtIndex).Cells(1).Value & "',CTNCODEREPAIR = '" & DataGridViewX1.Rows(inbtIndex).Cells(2).Value & "' , CTNCODE = '" & DataGridViewX1.Rows(inbtIndex).Cells(3).Value & "', CTNCODEETC = '" & DataGridViewX1.Rows(inbtIndex).Cells(4).Value & "' WHERE CTNDEPOTDETID = '" & DataGridViewX1.Rows(inbtIndex).Cells(11).Value & "'; "
                mySqlCommand.CommandText = commandText2
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = mysql

                mySqlCommand.ExecuteNonQuery()
                mysql.Close()
                MsgBox("แก้ไขข้อมูลเรียบร้อย")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

            mysql.Close()


         

        Else
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try
                mySqlCommand.Parameters.Clear()
                mySqlCommand.CommandText = "insert into ctndepotdet ( CTNDEPOTID, CTNCOMMENT, CTNPRICE,CTNPRICEDEPART,CTNDEPOTSIZE,CTNDEPOTHR,CTNCOMMENTENG,CTNCODEREPAIR,CTNCODE,CTNCODEETC) values (@CTNDEPOTID,@CTNCOMMENT,@CTNPRICE,@CTNPRICEDEPART,@CTNDEPOTSIZE,@CTNDEPOTHR,@CTNCOMMENTENG,@CTNCODEREPAIR,@CTNCODE,@CTNCODEETC)"
                mySqlCommand.Connection = mysql


                mySqlCommand.Parameters.AddWithValue("@CTNDEPOTID", iddepot)
                mySqlCommand.Parameters.AddWithValue("@CTNCOMMENT", DataGridViewX1.Rows(inbtIndex).Cells(0).Value)
                mySqlCommand.Parameters.AddWithValue("@CTNPRICE", DataGridViewX1.Rows(inbtIndex).Cells(7).Value)
                mySqlCommand.Parameters.AddWithValue("@CTNPRICEDEPART", DataGridViewX1.Rows(inbtIndex).Cells(8).Value)
                mySqlCommand.Parameters.AddWithValue("@CTNDEPOTSIZE", DataGridViewX1.Rows(inbtIndex).Cells(6).Value)
                mySqlCommand.Parameters.AddWithValue("@CTNDEPOTHR", DataGridViewX1.Rows(inbtIndex).Cells(5).Value)
                mySqlCommand.Parameters.AddWithValue("@CTNCOMMENTENG", DataGridViewX1.Rows(inbtIndex).Cells(1).Value)
                mySqlCommand.Parameters.AddWithValue("@CTNCODEREPAIR", DataGridViewX1.Rows(inbtIndex).Cells(2).Value)
                mySqlCommand.Parameters.AddWithValue("@CTNCODE", DataGridViewX1.Rows(inbtIndex).Cells(3).Value)
                mySqlCommand.Parameters.AddWithValue("@CTNCODEETC", DataGridViewX1.Rows(inbtIndex).Cells(4).Value)
                mySqlCommand.ExecuteNonQuery()
                mysql.Close()
                MsgBox("บันทึกข้อมูลเรียบร้อย")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try

        End If



    End Sub

    Private Sub Column10_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Column10.Click
        Dim respone As Object
        respone = MsgBox("ต้องการลบใช่หรือไม่", MsgBoxStyle.OkCancel + MsgBoxStyle.Information, "Warning Messsage")
        If respone = 1 Then


            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If
            Try

                mySqlCommand.CommandText = "DELETE FROM ctndepotdet where CTNDEPOTDETID = '" & DataGridViewX1.Rows(inbtIndex).Cells(11).Value & "';"
                mySqlCommand.CommandType = CommandType.Text
                mySqlCommand.Connection = mysql

                mySqlCommand.ExecuteNonQuery()
                mysql.Close()


            Catch ex As Exception

                MsgBox(ex.ToString)
                Exit Sub
            End Try
            mysql.Close()
            showlistview3()

        End If

    End Sub
    Private Sub Column11_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Column11.Click

        DataGridViewX1.Rows(inbtIndex).Cells(7).Value = CDbl(DataGridViewX1.Rows(inbtIndex).Cells(6).Value) * CDbl(DataGridViewX1.Rows(inbtIndex).Cells(5).Value)

    End Sub
    Private Sub ButtonItem6_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        If DataGridViewX2.Rows.Count > 0 Then
            FolderBrowserDialog1.Description = "Pick Folder to store Excecl files"
            FolderBrowserDialog1.ShowNewFolderButton = True
            FolderBrowserDialog1.SelectedPath = "C:\"
            If FolderBrowserDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then

                Try
                    Dim t = New Thread(New ThreadStart(AddressOf excelall))
                    t.Start()

                Catch ex As Exception

                End Try
            End If


        Else
            MsgBox("กรุณาเลือกตู้คอนเทนเนอร์")

        End If
    End Sub

    Private Sub excelall()
   
        Dim pathExcel As String
        Dim count As Integer = 7


        pathExcel = FolderBrowserDialog1.SelectedPath
        Dim excelapp As New Excel.Application
        Dim excelbooks As Excel.Workbook
        Dim excelsheets As Excel.Worksheet
        Dim excelsheets1 As Excel.Worksheet
        excelbooks = excelapp.Workbooks.Add
        'excelbooks.Windows(1).DisplayGridlines = False

        excelsheets = CType(excelbooks.Worksheets(1), Excel.Worksheet)
        With excelsheets


            Try
                .Shapes.AddPicture(AppDomain.CurrentDomain.BaseDirectory.ToString + "ykp_wanhai.png", False, True, 1, 1, 70, 70)
            Catch ex As Exception

            End Try
            .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
            .PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait

            .Range("A1").ColumnWidth = 4.75
            .Range("B1").ColumnWidth = 10.0
            .Range("C1").ColumnWidth = 4.63
            .Range("D1").ColumnWidth = 8.5
            .Range("E1").ColumnWidth = 13.25
            .Range("F1").ColumnWidth = 15.13
            .Range("G1").ColumnWidth = 5.25
            .Range("H1").ColumnWidth = 10.75
            .Range("I1").ColumnWidth = 11.13
            .Range("J1").ColumnWidth = 14.63
            For J = 7 To 10
                .Range("A6:B6").Borders(J).Weight = 3 ' xlThin
                .Range("C6:C6").Borders(J).Weight = 3 ' xlThin
                .Range("D6:D6").Borders(J).Weight = 3 ' xlThin
                .Range("E6:E6").Borders(J).Weight = 3 ' xlThin
                .Range("F6:F6").Borders(J).Weight = 3 ' xlThin
                .Range("G6:H6").Borders(J).Weight = 3 ' xlThin
                .Range("I6:J6").Borders(J).Weight = 3 ' xlThin
                .Range("A7:B7").Borders(J).Weight = 3 ' xlThin
                .Range("C7:C7").Borders(J).Weight = 3 ' xlThin
                .Range("D7:D7").Borders(J).Weight = 3 ' xlThin
                .Range("E7:E7").Borders(J).Weight = 3 ' xlThin
                .Range("F7:F7").Borders(J).Weight = 3 ' xlThin
                .Range("G7:H7").Borders(J).Weight = 3 ' xlThin
                .Range("I7:J7").Borders(J).Weight = 3 ' xlThin

                '-----
                .Range("A10:A10").Borders(J).Weight = 3 ' xlThin
                .Range("B10:E10").Borders(J).Weight = 3 ' xlThin
                .Range("F10:F10").Borders(J).Weight = 3 ' xlThin
                .Range("G10:G10").Borders(J).Weight = 3 ' xlThin
                .Range("H10:H10").Borders(J).Weight = 3 ' xlThin
                .Range("I10:I10").Borders(J).Weight = 3 ' xlThin
                .Range("J10:J10").Borders(J).Weight = 3 ' xlThin
                '----
                .Range("A31:G31").Borders(J).Weight = 3 ' xlThin
                .Range("H31:H31").Borders(J).Weight = 3 ' xlThin
                .Range("I31:I31").Borders(J).Weight = 3 ' xlThin
                .Range("J31:J31").Borders(J).Weight = 3 ' xlThin
            Next
            .Range("A11:A30").Borders(7).Weight = 3 ' xlThin
            .Range("B11:B30").Borders(7).Weight = 3 ' xlThin
            .Range("F11:F30").Borders(7).Weight = 3 ' xlThin
            .Range("G11:G30").Borders(7).Weight = 3 ' xlThin
            .Range("H11:H31").Borders(7).Weight = 3 ' xlThin
            .Range("I11:I31").Borders(7).Weight = 3 ' xlThin
            .Range("J11:J31").Borders(7).Weight = 3 ' xlThin
            .Range("K11:K31").Borders(7).Weight = 3 ' xlThin
            .Range("H39:J39").Borders(9).Weight = 2 ' xlThin
            .Range("A41:J41").Borders(9).Weight = 2 ' xlThin
            .Range("A47:J47").Borders(9).Weight = 2 ' xlThin
            .Range("A48:J48").Borders(9).Weight = 2 ' xlThin
            .Range("A1:L500").Font.Name = "Times New Roman"
            .Range("A1:L500").Font.Size = 12
            .Rows("1:1").rowheight = 30
            .Rows("2:4").rowheight = 20.25
            .Rows("5:9").rowheight = 16.5
            .Rows("10:10").rowheight = 17.25
            .Rows("11:11").rowheight = 15.75

            With .Range("A1:J1")
                .Merge()
                .Font.Size = 24
                .Value = "WAN HAI LINES LTD."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("E7:E7")
                .Merge()
                .Font.Size = 10
                .Value = "YUSOB PORT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A2:J2")
                .Merge()
                .Font.Size = 16
                .Value = "EQUIPMENT REPAIR ESTIMATION REPORT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("I4:I4")
                .Value = "EST NO.PEN"
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With

            With .Range("A6:B6")
                .Merge()
                .Font.Size = 10
                .Value = "CONTAINER NO"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A7:B7")
                .Merge()
                .Font.Size = 10
                .Value = ctnstring
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("C6:C6")
                .Merge()
                .Font.Size = 10
                .Value = "SIZE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D6:D6")
                .Merge()
                .Font.Size = 10
                .Value = "EST.DATE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("E6:E6")
                .Merge()
                .Font.Size = 10
                .Value = "LOADING PORT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("F6:F6")
                .Merge()
                .Font.Size = 10
                .Value = "IMPORT DATE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("G6:H6")
                .Merge()
                .Font.Size = 10
                .Value = "IMP VSL & VOY"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("I6:J6")
                .Merge()
                .Font.Size = 10
                .Value = "TOTAL COST"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A10:A10")
                .Merge()
                .Font.Size = 10
                .Value = "ITEM"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("B10:E10")
                .Merge()
                .Font.Size = 10
                .Value = "DESCRIPTION OF DAMAGE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("F10:F10")
                .Merge()
                .Font.Size = 10
                .Value = "REPAIR METHOD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("G10:G10")
                .Merge()
                .Font.Size = 10
                .Value = "CODE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("H10:H10")
                .Merge()
                .Font.Size = 10
                .Value = "MAN HOUR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("I10:I10")
                .Merge()
                .Font.Size = 10
                .Value = "LABOR COST"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("J10:J10")
                .Merge()
                .Font.Size = 10
                .Value = "MATERIAL COST"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("J31:J31")
                .Merge()
                .Font.Size = 10
                .Value = "TOTAL"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A33:C33")
                .Merge()
                .Font.Size = 10
                .Value = "LOCAL CURRENCY : MYR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
                .Font.Italic = True
            End With

            With .Range("A35:C35")
                .Merge()
                .Font.Size = 10
                .Value = "DAMAGED COST :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("A37:C37")
                .Merge()
                .Font.Size = 10
                .Value = "WEAR & TEAR COST :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("A39:C39")
                .Merge()
                .Font.Size = 10
                .Value = "CLEANING COST :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With
            With .Range("H39:I39")
                .Merge()
                .Font.Size = 10
                .Value = "APPROVED BY :"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With

            With .Range("A41:C41")
                .Merge()
                .Font.Size = 10
                .Value = "REPAIR METHOD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A42:D42")
                .Merge()
                .Font.Size = 10
                .Value = "WELD ----------------------- WLD"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A43:D43")
                .Merge()
                .Font.Size = 10
                .Value = "STRAIGHTEN------------- STR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A44:D44")
                .Merge()
                .Font.Size = 10
                .Value = "JACK UP ------------------- JAK"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A45:D45")
                .Merge()
                .Font.Size = 10
                .Value = "STR & WELD ------------- S&W"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A46:D46")
                .Merge()
                .Font.Size = 10
                .Value = "PATCH --------------------- PTH"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With





            With .Range("E42:F42")
                .Merge()
                .Font.Size = 10
                .Value = "SECTION ---------------------- SEC"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E43:F43")
                .Merge()
                .Font.Size = 10
                .Value = "REPLACE --------------------- RPL"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E44:F44")
                .Merge()
                .Font.Size = 10
                .Value = "INSERT ------------------------ INS"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E45:F45")
                .Merge()
                .Font.Size = 10
                .Value = "REFIT OR RESECURE ----- RFT"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("E46:F46")
                .Merge()
                .Font.Size = 10
                .Value = "REMOVE ---------------------- RMV"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With

            With .Range("G42:I42")
                .Merge()
                .Font.Size = 10
                .Value = "FREE --------------------------- FRE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A48:B48")
                .Merge()
                .Font.Size = 10
                .Value = "CODE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A49:J49")
                .Merge()
                .Font.Size = 10
                .Value = "DM --- DAMAGE COST = IMPACTED DAMAGE"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A50:J50")
                .Merge()
                .Font.Size = 10
                .Value = "WT --- DAMAGE COST = ANY KIND OF WEAR & TEAR"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            With .Range("A51:J51")
                .Merge()
                .Font.Size = 10
                .Value = "CN --- CLEANING COST = ANY KIND OF CLEANING"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
            End With
            Dim countline As Integer = 12
            Dim countitem As Integer = 1
            Dim man_hour As Double = 0
            Dim dmtotal As Integer = 0
            Dim wttotal As Integer = 0
            Dim cntotal As Integer = 0

            hrtotal = 0

            departotal = 0
            sumtotal = 0
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctndepot join ctndepotdet on ctndepot.CTNDEPOTID = ctndepotdet.CTNDEPOTID where ctndepot.CTNDEPOTID = '" & iddepot & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())


                    With .Range("A" + countline.ToString + ":A" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = countitem.ToString
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    countitem += 1

                    With .Range("B" + countline.ToString + ":E" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNCOMMENTENG")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlLeft
                        .Font.Bold = True

                    End With
                    With .Range("F" + countline.ToString + ":F" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNCODEREPAIR")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With
                    With .Range("G" + countline.ToString + ":G" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNCODE")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    With .Range("H" + countline.ToString + ":H" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNDEPOTHR")
                        .NumberFormat = "#,##0.00"
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    With .Range("I" + countline.ToString + ":I" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNPRICE")
                        .NumberFormat = "#,##0.00"
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    With .Range("J" + countline.ToString + ":J" + countline.ToString)
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNPRICEDEPART")
                        .NumberFormat = "#,##0.00"
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True

                    End With
                    countline += 1

                    man_hour += CDbl(mySqlReader("CTNDEPOTHR"))

                    hrtotal += CInt(mySqlReader("CTNPRICE"))
                    departotal += CInt(mySqlReader("CTNPRICEDEPART"))
                    sumtotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))

                    If mySqlReader("CTNCODE") = "DM" Then
                        dmtotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))
                    ElseIf mySqlReader("CTNCODE") = "WT" Then
                        wttotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))
                    ElseIf mySqlReader("CTNCODE") = "CN" Then
                        cntotal += CInt(mySqlReader("CTNPRICE")) + CInt(mySqlReader("CTNPRICEDEPART"))
                    End If

                End While
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mysql.Close()




            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctnmain join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID where CTNMAINID = '" & idctnnew & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())


                    With .Range("C7:C7")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNSIZE")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("F7:F7")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("VOYDATEEN")

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("G7:H7")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("VOYVESNAMEN") + "   N" + Format(mySqlReader("VOYAGEID"), "000")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With



                End While

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try



            With .Range("H31:H31")
                .Merge()
                .Font.Size = 10
                .Value = man_hour
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("I31:I31")
                .Merge()
                .Font.Size = 10
                .Value = hrtotal
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("J31:J31")
                .Merge()
                .Font.Size = 10
                .Value = departotal
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D35:D35")
                .Merge()
                .Font.Size = 10
                If dmtotal > 0 Then
                    .Value = dmtotal
                Else

                End If
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D37:D37")
                .Merge()
                .Font.Size = 10
                If wttotal > 0 Then
                    .Value = wttotal
                End If
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D39:D39")
                .Merge()
                .Font.Size = 10
                If cntotal > 0 Then
                    .Value = cntotal
                End If
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("I7:J7")
                .Merge()
                .Font.Size = 10
                .Value = sumtotal
                .NumberFormat = "#,##0.00"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

        End With

     

        excelsheets1 = CType(excelbooks.Worksheets(2), Excel.Worksheet)
        'excelbooks.Windows(2).DisplayGridlines = False


        With excelsheets1

            .PageSetup.PaperSize = Excel.XlPaperSize.xlPaperA4
            .PageSetup.Orientation = Excel.XlPageOrientation.xlPortrait
            Try
                .Shapes.AddPicture(AppDomain.CurrentDomain.BaseDirectory.ToString + "ykp_logo.jpg", False, True, 1, 1, 70, 70)
            Catch ex As Exception

            End Try
            .Range("A1").ColumnWidth = 4.75
            .Range("B1").ColumnWidth = 10.0
            .Range("C1").ColumnWidth = 6.13
            .Range("D1").ColumnWidth = 13.5
            .Range("E1").ColumnWidth = 13.25
            .Range("F1").ColumnWidth = 13.5
            .Range("G1").ColumnWidth = 5.25
            .Range("H1").ColumnWidth = 10.75

            .Range("A1:L20").Font.Name = "Angsana New"
            .Range("A1:L20").Font.Size = 14
            .Range("A21:L400").Font.Name = "Times New Roman"
            .Range("A21:L400").Font.Size = 9
            .Rows("4:8").rowheight = 0
            .Rows("9:46").rowheight = 15

            For J = 7 To 10
                .Range("A29:B30").Borders(J).Weight = 3 ' xlThin
                .Range("C29:C30").Borders(J).Weight = 3 ' xlThin
                .Range("D29:D30").Borders(J).Weight = 3 ' xlThin
                .Range("E29:E30").Borders(J).Weight = 3 ' xlThin
                .Range("F29:F30").Borders(J).Weight = 3 ' xlThin
                .Range("G29:H30").Borders(J).Weight = 3 ' xlThin

                .Range("A31:B31").Borders(J).Weight = 3 ' xlThin
                .Range("C31:C31").Borders(J).Weight = 3 ' xlThin
                .Range("D31:D31").Borders(J).Weight = 3 ' xlThin
                .Range("E31:E31").Borders(J).Weight = 3 ' xlThin
                .Range("F31:F31").Borders(J).Weight = 3 ' xlThin
                .Range("G31:H31").Borders(J).Weight = 3 ' xlThin
                '-----
                .Range("A33:A34").Borders(J).Weight = 3 ' xlThin
                .Range("B33:E34").Borders(J).Weight = 3 ' xlThin
                .Range("F33:F34").Borders(J).Weight = 3 ' xlThin
                .Range("G33:H34").Borders(J).Weight = 3 ' xlThin

            Next

            .Range("A31:A48").Borders(7).Weight = 3 ' xlThin
            .Range("B34:B48").Borders(7).Weight = 3 ' xlThin

            .Range("F34:F48").Borders(7).Weight = 3 ' xlThin

            .Range("G34:G48").Borders(7).Weight = 3 ' xlThin
            .Range("I34:I48").Borders(7).Weight = 3 ' xlThin


            .Range("A48:H48").Borders(9).Weight = 2 ' xlThin

            With .Range("A1:H1")
                .Merge()
                .Value = "Estimate Of  Repair"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("A29:B29")
                .Merge()
                .Value = "หมายเลขตู้"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A30:B30")
                .Merge()
                .Value = "Container No."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("C29:C29")
                .Merge()
                .Value = "ขนาดตู้"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("C30:C30")
                .Merge()
                .Value = "Size"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("D29:D29")
                .Merge()
                .Value = "วันที่ตรวจเช็ค"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("D30:D30")
                .Merge()
                .Value = "Date-check"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With


            With .Range("E29:E29")
                .Merge()
                .Value = "ชื่อท่าเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("E30:E30")
                .Merge()
                .Value = "Port Name"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("F29:F29")
                .Merge()
                .Value = "วันที่ถึงท่าเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("F30:F30")
                .Merge()
                .Value = "Date In"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With

            With .Range("G29:H29")
                .Merge()
                .Value = "ชื่อเรือ & เที่ยวเรือ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("G30:H30")
                .Merge()
                .Value = "Vessel & Voyage"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With




            With .Range("A33:A33")
                .Merge()
                .Value = "ลำดับ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With
            With .Range("A34:A34")
                .Merge()
                .Value = "Item"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
            End With



            With .Range("B33:E33")
                .Merge()
                .Value = "ลักษณะการซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("B34:E34")
                .Merge()
                .Value = "Manner Repairing"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With



            With .Range("F33:F33")
                .Merge()
                .Value = "โค๊ซซ่อม"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("F34:F34")
                .Merge()
                .Value = "Code  Repair"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With




            With .Range("G33:H33")
                .Merge()
                .Value = "โค๊ซ"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With
            With .Range("G34:H34")
                .Merge()
                .Value = "Code"
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlCenter
                .Font.Bold = True
                .Font.Italic = True
            End With








            With .Range("F50:H50")
                .Merge()
                .Font.Size = 10
                .Value = "Signature :........................."
                .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                .HorizontalAlignment = Excel.Constants.xlLeft
                .Font.Bold = True
            End With


            Dim countline As Integer = 35
            Dim countitem As Integer = 1
            Dim man_hour As Double = 0

            hrtotal = 0

            departotal = 0
            sumtotal = 0
            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctndepot join ctndepotdet on ctndepot.CTNDEPOTID = ctndepotdet.CTNDEPOTID  where ctndepot.CTNDEPOTID = '" & iddepot & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
            mySqlReader = mySqlCommand.ExecuteReader

            While (mySqlReader.Read())
                With .Range("A" + countline.ToString + ":A" + countline.ToString)
                    .Merge()
                    .Font.Size = 10
                    .Value = countitem.ToString
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True

                End With
                countitem += 1

                With .Range("B" + countline.ToString + ":E" + countline.ToString)
                    .Merge()
                    .Font.Size = 10
                    .Value = mySqlReader("CTNCOMMENT")
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlLeft
                    .Font.Bold = True

                End With
                With .Range("F" + countline.ToString + ":F" + countline.ToString)
                    .Merge()
                    .Font.Size = 10
                    .Value = mySqlReader("CTNCODEREPAIR")
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True
                End With
                With .Range("G" + countline.ToString + ":H" + countline.ToString)
                    .Merge()
                    .Font.Size = 10
                    .Value = mySqlReader("CTNCODE")
                    .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                    .HorizontalAlignment = Excel.Constants.xlCenter
                    .Font.Bold = True

                End With
                countline += 1


            End While

            mysql.Close()
            If mysql.State = ConnectionState.Closed Then
                mysql.Open()
            End If

            mySqlCommand.CommandText = "Select * from ctnmain join voyage on ctnmain.CTNVOYN = voyage.VOYAGEID where CTNMAINID = '" & idctnnew & "';"
            ' mySqlCommand.CommandText -0mySqlCommand.Connection = mysql

            mySqlCommand.Connection = mysql
            mySqlAdaptor.SelectCommand = mySqlCommand
            Try
                mySqlReader = mySqlCommand.ExecuteReader

                While (mySqlReader.Read())


                    With .Range("A31:B31")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNSTRING")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("C31:C31")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("CTNSIZE")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("E31:E31")
                        .Merge()
                        .Font.Size = 10
                        .Value = "YUSOB PORT"

                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

                    With .Range("F31:F31")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("VOYDATEEN")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

                    With .Range("G31:H31")
                        .Merge()
                        .Font.Size = 10
                        .Value = mySqlReader("VOYVESNAMEN") + "   N" + Format(mySqlReader("VOYAGEID"), "000")
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With


                    With .Range("D31:D31")
                        .Merge()
                        .Font.Size = 10
                        .Value = " "
                        .VerticalAlignment = Excel.XlVAlign.xlVAlignCenter
                        .HorizontalAlignment = Excel.Constants.xlCenter
                        .Font.Bold = True
                    End With

                End While

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try






        End With
        excelapp.Windows.Application.ActiveWindow.DisplayGridlines = False



        Try
            excelbooks.SaveAs(pathExcel.ToString + "\" + "ReportDepot" + Date.Now.Day.ToString + "-" + Date.Now.Month.ToString + "-" + Date.Now.Year.ToString + ".xlsx")


            MsgBox("Report Complete", MsgBoxStyle.Information, "Complete Report")
            excelsheets = Nothing
            excelsheets1 = Nothing
            excelbooks.Close()


            excelapp.Quit()

            excelbooks = Nothing


            excelapp = Nothing

        Catch ex As Exception
            MsgBox(ex.ToString)
            MsgBox("NOT SAVED")

        End Try

    End Sub

    Private Sub DataGridViewX1_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles DataGridViewX1.RowPostPaint
        Dim dg As DataGridView = DirectCast(sender, DataGridView)
        ' Current row record
        Dim rowNumber As String = (e.RowIndex + 1).ToString()

        ' Format row based on number of records displayed by using leading zeros
        While rowNumber.Length < dg.RowCount.ToString().Length
            rowNumber = "0" & rowNumber
        End While

        ' Position text
        Dim size As SizeF = e.Graphics.MeasureString(rowNumber, Me.Font)
        If dg.RowHeadersWidth < CInt(size.Width + 20) Then
            dg.RowHeadersWidth = CInt(size.Width + 20)
        End If

        ' Use default system text brush
        Dim b As Brush = SystemBrushes.ControlText

        ' Draw row number
        e.Graphics.DrawString(rowNumber, dg.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
    End Sub

    Private Sub DataGridViewX2_CellClick(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridViewX2.CellClick
        If e.RowIndex < 0 Then
            Exit Sub
        End If
        Try
            inbtIndex = e.RowIndex



        Catch ex As Exception

        End Try
        showlistview3()
    End Sub

    Private Sub ButtonX2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonX2.Click

        If DataGridViewX1.Rows.Count > 0 Then
            For i = 0 To DataGridViewX1.Rows.Count - 1

                If DataGridViewX1.Rows(i).Cells(11).Value > 0 Then

                    mysql.Close()
                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If
                    Try
                        Dim commandText2 As String
                        commandText2 = "UPDATE ctndepotdet SET  CTNDEPOTID = '" & iddepot & "' , CTNCOMMENT = '" & DataGridViewX1.Rows(i).Cells(0).Value & "' , CTNPRICE = '" & DataGridViewX1.Rows(inbtIndex).Cells(7).Value & "' , CTNPRICEDEPART = '" & DataGridViewX1.Rows(i).Cells(8).Value & "' , CTNDEPOTSIZE = '" & DataGridViewX1.Rows(i).Cells(6).Value & "' , CTNDEPOTHR = '" & DataGridViewX1.Rows(i).Cells(5).Value & "',CTNCOMMENTENG = '" & DataGridViewX1.Rows(i).Cells(1).Value & "',CTNCODEREPAIR = '" & DataGridViewX1.Rows(i).Cells(2).Value & "' , CTNCODE = '" & DataGridViewX1.Rows(i).Cells(3).Value & "', CTNCODEETC = '" & DataGridViewX1.Rows(i).Cells(4).Value & "' WHERE CTNDEPOTDETID = '" & DataGridViewX1.Rows(i).Cells(11).Value & "'; "
                        mySqlCommand.CommandText = commandText2
                        mySqlCommand.CommandType = CommandType.Text
                        mySqlCommand.Connection = mysql

                        mySqlCommand.ExecuteNonQuery()
                        mysql.Close()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try

                    mysql.Close()



                Else
                    If mysql.State = ConnectionState.Closed Then
                        mysql.Open()
                    End If
                    Try
                        mySqlCommand.Parameters.Clear()
                        mySqlCommand.CommandText = "insert into ctndepotdet ( CTNDEPOTID, CTNCOMMENT, CTNPRICE,CTNPRICEDEPART,CTNDEPOTSIZE,CTNDEPOTHR,CTNCOMMENTENG,CTNCODEREPAIR,CTNCODE,CTNCODEETC) values (@CTNDEPOTID,@CTNCOMMENT,@CTNPRICE,@CTNPRICEDEPART,@CTNDEPOTSIZE,@CTNDEPOTHR,@CTNCOMMENTENG,@CTNCODEREPAIR,@CTNCODE,@CTNCODEETC)"
                        mySqlCommand.Connection = mysql


                        mySqlCommand.Parameters.AddWithValue("@CTNDEPOTID", iddepot)
                        mySqlCommand.Parameters.AddWithValue("@CTNCOMMENT", DataGridViewX1.Rows(i).Cells(0).Value)
                        mySqlCommand.Parameters.AddWithValue("@CTNPRICE", DataGridViewX1.Rows(i).Cells(7).Value)
                        mySqlCommand.Parameters.AddWithValue("@CTNPRICEDEPART", DataGridViewX1.Rows(i).Cells(8).Value)
                        mySqlCommand.Parameters.AddWithValue("@CTNDEPOTSIZE", DataGridViewX1.Rows(i).Cells(6).Value)
                        mySqlCommand.Parameters.AddWithValue("@CTNDEPOTHR", DataGridViewX1.Rows(i).Cells(5).Value)
                        mySqlCommand.Parameters.AddWithValue("@CTNCOMMENTENG", DataGridViewX1.Rows(i).Cells(1).Value)
                        mySqlCommand.Parameters.AddWithValue("@CTNCODEREPAIR", DataGridViewX1.Rows(i).Cells(2).Value)
                        mySqlCommand.Parameters.AddWithValue("@CTNCODE", DataGridViewX1.Rows(i).Cells(3).Value)
                        mySqlCommand.Parameters.AddWithValue("@CTNCODEETC", DataGridViewX1.Rows(i).Cells(4).Value)
                        mySqlCommand.ExecuteNonQuery()
                        mysql.Close()

                    Catch ex As Exception
                        MsgBox(ex.ToString)
                    End Try


                End If






            Next
        End If

        MsgBox("บันทึกข้อมูลเรียบร้อย")

    End Sub

    Private Sub DataGridViewX2_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles DataGridViewX2.RowPostPaint
        Dim dg As DataGridView = DirectCast(sender, DataGridView)
        ' Current row record
        Dim rowNumber As String = (e.RowIndex + 1).ToString()

        ' Format row based on number of records displayed by using leading zeros
        While rowNumber.Length < dg.RowCount.ToString().Length
            rowNumber = "0" & rowNumber
        End While

        ' Position text
        Dim size As SizeF = e.Graphics.MeasureString(rowNumber, Me.Font)
        If dg.RowHeadersWidth < CInt(size.Width + 20) Then
            dg.RowHeadersWidth = CInt(size.Width + 20)
        End If

        ' Use default system text brush
        Dim b As Brush = SystemBrushes.ControlText

        ' Draw row number
        e.Graphics.DrawString(rowNumber, dg.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))

    End Sub

    Private Sub TextBoxX2_KeyDown(ByVal sender As Object, ByVal e As System.Windows.Forms.KeyEventArgs) Handles TextBoxX2.KeyDown
        If e.KeyCode = Keys.Enter Then
            searchCTN()
        End If
    End Sub
End Class